import oracledb as ora
import pandas as pd

class OraWorker(object):

    def __init__(self,user:str,password:str,dsn:str,client:str) -> None:
        try:
            ora.init_oracle_client(lib_dir=client)
            self.connection = ora.connect(dsn=dsn,user=user,password=password)
            self.cursor = self.connection.cursor()
        except: raise
    
    def ora_version(self)->str:
        try:
            res = []
            for row in self.cursor.execute("SELECT * FROM v$version"):
                res.append(f"{row}")
            # for row in self.cursor.execute("SELECT * FROM v$instance"):
            #     res.append(f"{row}")
            # for row in self.cursor.execute("select sysdate as dt, 1 as ver from dual"):
            #     res.append(f"{row[0]}")
            return "\n".join(res)
        except :raise

    def product_exists(self,product:str)->bool:
        try:
            # print(product)
            self.cursor.execute("select count(1) from ione.items where state='A' and code_name=:code",[product])
            result=self.cursor.fetchone()[0]
            return result==1
        except: raise
        
    def product_check(self,product:str,nomenclature:str)->tuple:
        try:
            sql="""select  
                    (select nvl(max(m.scp_product_name),'нет') from scm_fact.product_map_sap_scp m where m.sap_product_code=:NOM) nom_to_up,
                    (select count(1) from ione.items where state='A' and code_name=:UP) exists_in_up
                    from dual"""
            self.cursor.execute(sql,[nomenclature,product])
            record = self.cursor.fetchone()
            correct = 1 if record[0]==product else 0
            return (record[0],record[1],correct)
        except Exception as exp : return(f"{exp}",0,0)
    
    def close(self):
        try:
            self.cursor.close()
            self.connection.close()
        except: raise